<?php

class Model_Book
{

    public static function getAll ($params, &$count)
    {
        try
        {
            $params['Offset'] = ($params['Page'] - 1) * $params['Limit'];
            
            $db = Core_Global::getDatabase();
            
            $stmt = $db->prepare(
                    'SELECT SQL_CALC_FOUND_ROWS
                        Book.*, 
                        Publisher.Name AS Publisher,                         
                        GROUP_CONCAT(Author.Name) AS Author,
                        GROUP_CONCAT(Author.AuthorID) AS AuthorID,
                        GROUP_CONCAT(Category.Name) AS Category,
                        GROUP_CONCAT(Category.CategoryID) AS CategoryID,
                        Book.Quantity - (SELECT count(*) FROM Transaction WHERE BookID = Book.BookID AND Type = :p_Trans_Borrow AND Status = 1) AS Residue,
                        Book.Quantity - (SELECT count(*) FROM Transaction WHERE BookID = Book.BookID AND Type = :p_Trans_Borrow AND Status = 1) - (SELECT count(*) AS Pending FROM Transaction WHERE BookID = Book.BookID AND Type = :p_Trans_Pending AND Status = 1) AS InStock                        
                    FROM Book 
                    INNER JOIN Publisher USING(PublisherID)
                    INNER JOIN Book_Author ON (Book.BookID = Book_Author.BookID)
                    INNER JOIN Author ON (Book_Author.AuthorID = Author.AuthorID)
                    INNER JOIN Book_Category ON (Book.BookID = Book_Category.BookID)
                    INNER JOIN Category ON (Book_Category.CategoryID = Category.CategoryID)                   
                    WHERE Book.Status = :p_Status
                    GROUP BY Book.BookID
                    LIMIT :p_Offset, :p_Limit');
                        
            $stmt->bindValue(':p_Trans_Pending', Model_Transaction::PENDING, PDO::PARAM_INT);
            $stmt->bindValue(':p_Trans_Borrow', Model_Transaction::BORROW, PDO::PARAM_INT);
            $stmt->bindValue(':p_Status', $params['Status'], PDO::PARAM_STR);
            $stmt->bindValue(':p_Limit', $params['Limit'], PDO::PARAM_INT);
            $stmt->bindValue(':p_Offset', $params['Offset'], PDO::PARAM_INT);
            
            $stmt->execute();                        
            
            $data = $stmt->fetchAll();
            
            $stmt = $db->query('SELECT FOUND_ROWS()');            
            
            $count = $stmt->fetchColumn();
            
            return $data;
        }
        catch (Zend_Db_Exception $e)
        {
            throw new Zend_Db_Exception($e);
        }
    }

    public static function get ($params)
    {
        try
        {            
            $db = Core_Global::getDatabase();
            
            $stmt = $db->prepare(
                    'SELECT 
                        Book.*, 
                        Publisher.Name AS Publisher,                         
                        GROUP_CONCAT(Author.Name) AS Author,
                        GROUP_CONCAT(Author.AuthorID) AS AuthorID,
                        GROUP_CONCAT(Category.Name) AS Category,
                        GROUP_CONCAT(Category.CategoryID) AS CategoryID,
                        Book.Quantity - (SELECT count(*) FROM Transaction WHERE BookID = Book.BookID AND Type = :p_Trans_Borrow  AND Status = 1) AS Residue,
                        Book.Quantity - (SELECT count(*) FROM Transaction WHERE BookID = Book.BookID AND Type = :p_Trans_Borrow  AND Status = 1) - (SELECT count(*) AS Pending FROM Transaction WHERE BookID = Book.BookID AND Type = :p_Trans_Pending AND Status = 1) AS InStock
                    FROM Book 
                    INNER JOIN Publisher USING(PublisherID)
                    INNER JOIN Book_Author ON (Book.BookID = Book_Author.BookID)
                    INNER JOIN Author ON (Book_Author.AuthorID = Author.AuthorID)
                    INNER JOIN Book_Category ON (Book.BookID = Book_Category.BookID)
                    INNER JOIN Category ON (Book_Category.CategoryID = Category.CategoryID)                   
                    WHERE Book.BookID = :p_BookID AND Book.Status = :p_Status
                    GROUP BY Book.BookID');
            
            $stmt->bindValue(':p_Trans_Pending', Model_Transaction::PENDING, PDO::PARAM_INT);
            $stmt->bindValue(':p_Trans_Borrow', Model_Transaction::BORROW, PDO::PARAM_INT);
            $stmt->bindValue(':p_BookID', $params['book_id'], PDO::PARAM_STR);
            $stmt->bindValue(':p_Status', 1, PDO::PARAM_STR);            
            
            $stmt->execute();    
            
            return $stmt->fetch();
        }
        catch (Zend_Db_Exception $e)
        {
            throw new Zend_Db_Exception($e);
        }
    }

    public static function find ($params, &$count)
    {
        try
        {            
            $db = Core_Global::getDatabase();
            
            $params['Offset'] = ($params['Page'] - 1) * $params['Limit'];
            
            $sql = 'SELECT 
                        Book.*, 
                        Publisher.Name AS Publisher,                         
                        GROUP_CONCAT(Author.Name) AS Author,
                        GROUP_CONCAT(Author.AuthorID) AS AuthorID,
                        GROUP_CONCAT(Category.Name) AS Category,
                        GROUP_CONCAT(Category.CategoryID) AS CategoryID,
                        Book.Residue - (SELECT count(*) AS Pending FROM Transaction WHERE BookID = Book.BookID AND Type = :p_Trans_Type) AS InStock
                    FROM Book 
                    INNER JOIN Publisher USING(PublisherID)
                    INNER JOIN Book_Author ON (Book.BookID = Book_Author.BookID)
                    INNER JOIN Author ON (Book_Author.AuthorID = Author.AuthorID)
                    INNER JOIN Book_Category ON (Book.BookID = Book_Category.BookID)
                    INNER JOIN Category ON (Book_Category.CategoryID = Category.CategoryID)                   
                    WHERE Book.Status = :p_Status';
            
            if ($params['CategoryID'])
            {
                $sql .= ' AND Category.CategoryID = :p_CategoryID';
            }
            
            if ($params['q'])
            {
                $sql .= ' AND Title LIKE :p_Query';
            }
            
            $sql .= ' GROUP BY Book.BookID LIMIT :p_Offset, :p_Limit';
            
            $stmt = $db->prepare($sql);
            
            if ($params['CategoryID'])
            {
                $stmt->bindValue(':p_CategoryID', $params['CategoryID'], PDO::PARAM_INT);
            }
            
            if ($params['q'])
            {
                $stmt->bindValue(':p_Query', '%' . $params['q'] . '%', PDO::PARAM_STR);
            }

            $stmt->bindValue(':p_Trans_Type', Model_Transaction::PENDING, PDO::PARAM_INT);            
            $stmt->bindValue(':p_Status', $params['Status'], PDO::PARAM_INT);
            $stmt->bindValue(':p_Limit', $params['Limit'], PDO::PARAM_INT);
            $stmt->bindValue(':p_Offset', $params['Offset'], PDO::PARAM_INT);
            
            $stmt->execute();                        
            
            $data = $stmt->fetchAll();
            
            $stmt = $db->query('SELECT FOUND_ROWS()');            
            
            $count = $stmt->fetchColumn();
            
            return $data;
        }
        catch (Zend_Db_Exception $e)
        {
            throw new Zend_Db_Exception($e);
        }
    }

    public static function create ($params)
    {
        try
        {
            
            $db = Core_Global::getDatabase();
            
            $stmt = $db->query('INSERT INTO Book VALUES (?,?,?,?,?,?,?,?,?)', 
                    array(
                            null,
                            $params['PublisherID'],
                            $params['Title'],
                            null,
                            $params['Cover'],
                            $params['Quantity'],
                            $params['Quantity'],
                            intval(microtime(true)),
                            1
                    ));
            
            $bookId = $db->lastInsertId();
            
            foreach ($params['AuthorID'] as $authorId)
            {
                $db->query('INSERT INTO Book_Author VALUES (?,?)', 
                        array(
                                $bookId,
                                $authorId
                        ));
            }
            
            foreach ($params['CategoryID'] as $categoryId)
            {
                $db->query('INSERT INTO Book_Category VALUES (?,?)', 
                        array(
                                $bookId,
                                $categoryId
                        ));
            }
            
            return $bookId;
        }
        catch (Zend_Db_Exception $e)
        {
            throw new Zend_Db_Exception($e);
        }
    }

    public static function edit ($params)
    {
        try
        {
            
            $db = Core_Global::getDatabase();            
            
            $stmt = $db->query(
                    'UPDATE Book SET 
                                    Title = ?,
                                    PublisherID = ?,
                                    CoverPath = ?,
                                    Quantity = ?,
                                    Residue = ?
                                WHERE BookID = ?', 
                    array(
                            $params['Title'],
                            $params['PublisherID'],
                            $params['Cover'],
                            $params['Quantity'],
                            $params['Residue'],
                            $params['BookID']
                    ));
            
            $stmt = $db->query('DELETE FROM Book_Author WHERE BookID = ?', array($params['BookID']));
            $stmt = $db->query('DELETE FROM Book_Category WHERE BookID = ?', array($params['BookID']));
            
            foreach ($params['AuthorID'] as $authorId)
            {
                $db->query('INSERT INTO Book_Author VALUES (?,?)',
                        array(
                                $params['BookID'],
                                $authorId
                        ));
            }
            
            foreach ($params['CategoryID'] as $categoryId)
            {
                $db->query('INSERT INTO Book_Category VALUES (?,?)',
                        array(
                                $params['BookID'],
                                $categoryId
                        ));
            }
            
            return $stmt->rowCount();
        }
        catch (Zend_Db_Exception $e)
        {
            throw new Zend_Db_Exception($e);
        }
    }

    public static function delete ($params)
    {
        try
       {            
            $db = Core_Global::getDatabase();
                        
            $book = self::get(array('book_id' => $params['BookID']));
            
            if ($book['CoverPath'])
            {
                @unlink(UPLOAD_PATH . '/covers/' . $book['CoverPath']);
                @unlink(UPLOAD_PATH . '/covers/thumb/' . $book['CoverPath']);
            }
            
            $stmt = $db->query('DELETE FROM Book WHERE BookID = ?', array(
                    $params['BookID']
            ));
            
            $affected = $stmt->rowCount();
            
            if ($affected)
            {
                $db->query('DELETE FROM Book_Author WHERE BookID = ?', array(
                        $params['BookID']
                ));
                
                $db->query('DELETE FROM Book_Category WHERE BookID = ?', array(
                        $params['BookID']
                ));
            }            
            
            return $affected;
        }
        catch (Zend_Db_Exception $e)
        {
            throw new Zend_Db_Exception($e);
        }
    }
}
